﻿CREATE TRIGGER [dbo].[tr_Borrower_CheckVIN]
ON [dbo].[v_Borrower_CheckVIN]
INSTEAD OF INSERT AS

DECLARE @ErrMsg nvarchar(max)
SELECT @ErrMsg = ISNULL(@ErrMsg+CHAR(10),'')+dbo.sprint4(
'SSN:%s VIN:%s
LoanID:%s Status:%s',DUPs.SSN,DUPs.VIN,dbo.ShowLoan(L_B.LoanID),REPLACE(S.NameLong,'\n','\ N'))
FROM
(	SELECT B.ID,B.SSN,CI.VIN
	FROM inserted AS i
	INNER JOIN t_Loan_Borrower L_B ON i.ID = L_B.BorrowerID
	INNER JOIN t_Borrower B ON L_B.BorrowerID = B.ID
	INNER JOIN t_CarInfo CI ON CI.LoanID = L_B.LoanID
	WHERE VIN > ''
	GROUP BY B.ID,B.SSN,CI.VIN
	HAVING COUNT(*) > 1
)DUPs
INNER JOIN t_Loan_Borrower L_B ON L_B.BorrowerID = DUPs.ID
INNER JOIN inserted i ON ISNULL(i.LoanID,0) <> L_B.LoanID
INNER JOIN t_Loan L ON L.ID = L_B.LoanID
INNER JOIN v_State S ON S.ID = L.StatusID
GROUP BY DUPs.SSN,L_B.LoanID,DUPs.VIN,S.NameLong

IF @@ROWCOUNT = 0 RETURN

ROLLBACK
RAISERROR('This borrower already has a loan with the same VIN:

%s',16,1,@ErrMsg)





